package com.pet.server.ext.service;

import java.util.List;

import com.pet.server.ext.ApiError;
import com.pet.server.ext.Config;
import com.pet.server.ext.action.square.Adoptitem;
import com.pet.server.ext.constants.BaseAttribute;
import com.pet.server.ext.constants.Status.DailyTask;
import com.pet.server.ext.constants.Status.NormalTask;
import com.pet.server.ext.data.AnswerInfo;
import com.pet.server.ext.data.Campaign;
import com.pet.server.ext.data.Constant;
import com.pet.server.ext.data.Image;
import com.pet.server.ext.data.Option;
import com.pet.server.ext.data.Page;
import com.pet.server.ext.data.PetAdopt;
import com.pet.server.ext.data.PetAdoptItem;
import com.pet.server.ext.data.PetEvent;
import com.pet.server.ext.data.PetInfo;
import com.pet.server.ext.data.PetMissing;
import com.pet.server.ext.data.RequestInfo;
import com.pet.server.ext.data.TaskInfo;
import com.pet.server.ext.data.TimeLine;
import com.pet.server.ext.data.UserInfo;
import com.pet.server.ext.data.UserOption;
import com.pet.server.ext.data.UserTask;
import com.pet.server.ext.data.UserTaskDaily;
import com.pet.server.ext.dbutil.JdbcTemplate;
import com.pet.server.ext.dbutil.Sql;


public class UserService {
	private JdbcTemplate jdbcTemplate = new JdbcTemplate();

	public UserInfo findUserInfoById(Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_info u WHERE u.user_id=?");
		sql.addParam(userId);
		return jdbcTemplate.findObject(sql, UserInfo.class);
	}
	
	public UserInfo findUserInfoByUserName(String userName) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_info u WHERE u.user_name=?");
		sql.addParam(userName);
		return jdbcTemplate.findObject(sql, UserInfo.class);
	}
	public UserInfo findUserInfoByLogin(String userName,String password) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_info u WHERE u.user_name=? AND u.password=?");
		sql.addParam(userName,password);
		return jdbcTemplate.findObject(sql, UserInfo.class);
	}
	public UserInfo findUserInfoBynickName(String nickName) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_info u WHERE u.nick_name=?");
		sql.addParam(nickName);
		return jdbcTemplate.findObject(sql, UserInfo.class);
	}
	public UserInfo saveUserInfo(UserInfo u) throws ApiError{
		Sql sql = new Sql("INSERT INTO user_info (user_name, user_type, nick_name, password,ico_path) VALUES (?,?,?,?,?)");
		    sql.addParam(u.user_name,u.user_type,u.nick_name,u.password,u.ico_path);
		    Integer id = jdbcTemplate.saveObject(sql);
		    u.user_id=id;
		    return u;
	}
	public PetInfo findPetInfoById(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_info p WHERE p.pet_id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, PetInfo.class);
	}
	public void updatePetInfoById(PetInfo p) throws ApiError{
		Sql sql = new Sql("UPDATE pet_info p SET p.birthday=?,p.breed=?,p.pet_cate=?,p.ico_path=?,p.pet_name=?,p.sex=? WHERE p.pet_id=?");
		    sql.addParam(p.birthday,p.breed,p.pet_cate,p.ico_path,p.pet_name,p.sex,p.pet_id);
		    
		    //System.out.println(p.birthday+p.breed+p.pet_cate+p.ico_path+p.pet_name+p.pet_id);
		    jdbcTemplate.updateObject(sql);
	}
	public void updatePetInfo4remove(PetInfo p) throws ApiError{
		Sql sql = new Sql("UPDATE pet_info p SET p.state=? WHERE p.pet_id=?");
		    sql.addParam(BaseAttribute.LOGIC_FALSE,p.pet_id);
		    jdbcTemplate.updateObject(sql);
	}
	public List<PetInfo> queryPetInfoByUserId(Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_info p WHERE p.user_id=? AND p.state=?");
		    sql.addParam(userId,BaseAttribute.LOGIC_TRUE);
		return jdbcTemplate.queryForList(sql, PetInfo.class);
	}
	public void savePetInfo(PetInfo pet) throws ApiError{
		Sql sql = new Sql("INSERT INTO pet_info ( user_id, pet_name, pet_cate, sex, birthday, breed, ico_path) VALUES (?,?,?,?,?,?,?)");
	    sql.addParam(pet.user_id,pet.pet_name,pet.pet_cate,pet.sex,pet.birthday,pet.breed,pet.ico_path);
	    Integer id = jdbcTemplate.saveObject(sql);
	    pet.pet_id=id;
	}
	public List<PetAdoptItem> queryAdoptItem(Integer adoptId) throws ApiError{
		Sql sql = new Sql("SELECT pai.*,u.nick_name,u.user_id,u.ico_path,u.sex FROM pet_adopt_item pai,user_info u WHERE pai.user_id=u.user_id AND pai.adopt_id=?");
		    sql.addParam(adoptId);
		    sql.append(" ORDER BY pai.id DESC");
		return jdbcTemplate.queryForList(sql, PetAdoptItem.class);    
	}
	
	
	public PetMissing savePetMissing(PetMissing pm) throws ApiError{
		//Sql sql = new Sql("INSERT INTO pet_missing (user_id, pet_cate, feature, sex, age, breed, ico_path, miss_date, description) ");
		Sql sql = new Sql("	INSERT INTO pet_missing (user_id, city, area, longitude, latitude, pet_cate, feature, sex, age,ico_path, breed,  miss_date,phone, description)");    
		    sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		    sql.addParam(pm.user_id,pm.city,pm.area,pm.longitude,pm.latitude,pm.pet_cate,pm.feature,pm.sex,pm.age,pm.ico_path,pm.breed,pm.miss_date,pm.phone,pm.description);
		    Integer id = jdbcTemplate.saveObject(sql);
		    pm.miss_id=id;
		    return pm;
	}
	public PetAdopt savePetAdopt(PetAdopt pa) throws ApiError{
		Sql sql = new Sql("INSERT INTO pet_adopt ( user_id, pet_cate, sex,is_birth,is_injection, age,city,area, breed, ico_path, phone, qq, description) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
		    sql.addParam(pa.user_id,pa.pet_cate,pa.sex,pa.is_birth,pa.is_injection,pa.age,pa.city,pa.area,pa.breed,pa.ico_path,pa.phone,pa.qq,pa.description);
		    Integer id = jdbcTemplate.saveObject(sql);
		    pa.adopt_id=id;
		return pa;
	}
	public void updateForRemove(Integer adoptId) throws ApiError{
		Sql sql = new Sql("UPDATE pet_adopt pa SET pa.state=? WHERE pa.adopt_id=?");
		    sql.addParam(BaseAttribute.adopt_remove,adoptId);
		    jdbcTemplate.updateObject(sql);
	}
	
	public void searchPetAdoptbad(Page page,Integer userId,String code,String area,String cate) throws ApiError{
		Sql sql = new Sql("SELECT pa.*,c.conts_name AS breed_name FROM pet_adopt pa,constant c WHERE pa.breed=c.code and pa.state!=? ");
		    sql.addParam(BaseAttribute.adopt_remove);
		    sql.append("and", "pa.user_id", "=", userId);    
		    sql.append("and", "pa.pet_cate", "=", cate); 
		    sql.append("and", "pa.city", "=", code);
		    sql.append(" ORDER BY pa.adopt_id DESC");
		    jdbcTemplate.search(page, sql, PetAdopt.class);
	}
	
	public void searchPetAdopt(Page page,Integer userId,String code,String area,String cate,Integer selectUser) throws ApiError{
		Sql sql = new Sql("SELECT pa.*,(SELECT '1' FROM pet_adopt_item pai WHERE pai.user_id=? AND pai.adopt_id=pa.adopt_id) AS is_apply FROM pet_adopt pa WHERE pa.state!=?");
		//Sql sql = new Sql("SELECT pa.* FROM pet_adopt pa WHERE pa.state!=? ");
		    sql.addParam(userId,BaseAttribute.adopt_remove);
		    sql.append("and", "pa.user_id", "=", selectUser);
		    sql.append("and", "pa.pet_cate", "=", cate); 
		    sql.append("and", "pa.city", "=", code);
		    sql.append(" ORDER BY pa.adopt_id DESC");
		    jdbcTemplate.search(page, sql, PetAdopt.class);
	}
	
	
	public void saveImage(Image image) throws ApiError{
		Sql sql = new Sql("INSERT INTO image (oid, type, src) VALUES (?,?,?)");
		    sql.addParam(image.oid,image.type,image.src);
		    jdbcTemplate.saveObject(sql);
	}
	public List<Image> queryImage(Integer oid,String type) throws ApiError{
		Sql sql = new Sql("SELECT * FROM  image img WHERE img.oid=? AND img.type=?");
		    sql.addParam(oid,type);
		    return jdbcTemplate.queryForList(sql, Image.class);
	}
	public Image findImage(Integer oid,String type) throws ApiError{
		Sql sql = new Sql("SELECT * FROM  image img WHERE img.oid=? AND img.type=? LIMIT 1");
		    sql.addParam(oid,type);
		    return jdbcTemplate.findObject(sql, Image.class);
	}
	public void searchPetMissing(Page page,Integer userId,String code,String area,String cate) throws ApiError{
		Sql sql = new Sql("SELECT pm.* FROM pet_missing pm WHERE 1=1 ");
		//Sql sql = new Sql("SELECT pm.*,c.conts_name AS breed_name FROM pet_missing pm,constant c WHERE pm.breed=c.code ");
		    sql.append("and", "pm.user_id", "=", userId);    
		    sql.append("and", "pm.pet_cate", "=", cate); 
		    sql.append("and", "pm.city", "=", code);
		    sql.appendLike(null, "and", "pm.area", area);
		    sql.append(" ORDER BY pm.miss_id DESC");
		    jdbcTemplate.search(page, sql, PetMissing.class);
	}
	public List<PetMissing> queryPetMissing(Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT pm.* FROM pet_missing pm WHERE pm.user_id=?");
		    sql.addParam(userId);
		    sql.append(" ORDER BY pm.miss_id DESC");
		return jdbcTemplate.queryForList(sql, PetMissing.class);
	}
	
	public PetMissing findPetMissing(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT pm.* FROM pet_missing pm WHERE pm.miss_id=?");
		    sql.addParam(id);
		    return jdbcTemplate.findObject(sql, PetMissing.class);
	}
	public PetAdopt findAdoptById(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_adopt pa WHERE pa.adopt_id=?");
		    sql.addParam(id);
		    return jdbcTemplate.findObject(sql, PetAdopt.class);
	}
	public boolean adoptIsApplay(Integer adoptId,Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_adopt_item pai WHERE pai.adopt_id=? AND pai.user_id=?");
	        sql.addParam(adoptId,userId);
	        return jdbcTemplate.queryForList(sql, PetAdoptItem.class).iterator().hasNext();
	}
	
	public void updateAdoptState(Integer id) throws ApiError{
		Sql sql = new Sql("UPDATE pet_adopt pa SET pa.state=? WHERE pa.adopt_id=?");
		    sql.addParam(BaseAttribute.LOGIC_TRUE,id);
		    jdbcTemplate.updateObject(sql);
	}
	public void updateMissingState(Integer id) throws ApiError{
		Sql sql = new Sql("UPDATE pet_missing pm SET pm.state=? WHERE pm.miss_id=?");
		sql.addParam(BaseAttribute.LOGIC_TRUE,id);
		jdbcTemplate.updateObject(sql);
	}
	
	
	public void saveAnswerInfo(AnswerInfo aw) throws ApiError{
		Sql sql = new Sql("INSERT INTO answer_info ( reply_id, oid, answer_type, user_id, answer_text,sessionid) VALUES (?,?,?,?,?,?)");
		    sql.addParam(aw.reply_id,aw.oid,aw.answer_type,aw.user_id,aw.answer_text,aw.sessionid);
		    Integer id = jdbcTemplate.saveObject(sql);
		    aw.answer_id=id;
	}
	public PetMissing loadPetMissing(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_missing pm WHERE pm.miss_id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, PetMissing.class);
	}
	public PetAdopt loadPetAdopt(Integer id,Integer userId) throws ApiError{
		//Sql sql = new Sql("SELECT * FROM pet_adopt pa WHERE pa.adopt_id=?");
		Sql sql = new Sql("SELECT pa.*,(SELECT '1' FROM pet_adopt_item pai WHERE pai.user_id=? AND pai.adopt_id=pa.adopt_id) AS is_apply FROM pet_adopt pa WHERE pa.adopt_id=?");
		sql.addParam(userId,id);
		return jdbcTemplate.findObject(sql, PetAdopt.class);
	}
	
	public List<AnswerInfo> queryAnswerInfo(Integer id,String answer_type) throws ApiError{
		Sql sql = new Sql("SELECT a.*,u.ico_path,u.nick_name FROM answer_info a ,user_info u WHERE a.user_id=u.user_id AND a.oid=? AND a.answer_type=?");
		    sql.addParam(id,answer_type);
		return jdbcTemplate.queryForList(sql, AnswerInfo.class);
	}
	public void searchAnswerInfo(Integer id,String answer_type,Page page) throws ApiError{
		//System.out.println(id+"          "+answer_type);
		Sql sql = new Sql("SELECT a.*,u.ico_path,u.nick_name FROM answer_info a ,user_info u WHERE a.user_id=u.user_id AND a.oid=? AND a.answer_type=?");
		//Sql sql = new Sql("SELECT * FROM answer_info ai WHERE ai.oid=? AND ai.answer_type=?");
		    sql.addParam(id,answer_type);
		jdbcTemplate.search(page, sql, AnswerInfo.class);//(sql, AnswerInfo.class);
	}

	
	
	public void savePetAdoptItem(PetAdoptItem item) throws ApiError{
		Sql sql = new Sql("INSERT INTO pet_adopt_item (adopt_id, user_id, qq, phone, description) VALUES (?,?,?,?,?)");
		    sql.addParam(item.adopt_id,item.user_id,item.qq,item.phone,item.description);
		    jdbcTemplate.saveObject(sql);
	}
	
	
	public List<PetAdopt> queryPetAdoptByItemUserId(Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT pa.* FROM pet_adopt_item pai,pet_adopt pa WHERE pai.adopt_id=pa.adopt_id AND pai.user_id=? ");
		    sql.addParam(userId);
		    //sql.append("and", "pai.user_id", "=", ownerId);
		return jdbcTemplate.queryForList(sql, PetAdopt.class);
	}
	
	public Integer findPetItemCountByAdoptId(Integer adoptId) throws ApiError{
		Sql sql = new Sql("SELECT COUNT(1) FROM pet_adopt_item pai WHERE pai.adopt_id=?");
		    sql.addParam(adoptId);
		return jdbcTemplate.findCount(sql);
	}
	
	
	
	public List<Constant> queryConstantBypcode(String pcode,String state) throws ApiError{
		Sql sql = new Sql("SELECT * FROM constant c WHERE c.pcode=?");
		    sql.append("and", "c.display", "=", state);
		    sql.addParam(pcode);
		    return jdbcTemplate.queryForList(sql, Constant.class);
	}
	public Constant findConstantByCode(String code) throws ApiError{
		Sql sql = new Sql("SELECT * FROM constant c WHERE c.code=?");
	    sql.addParam(code);
	    return jdbcTemplate.findObject(sql, Constant.class);
	}
	public void saveTimeLine(TimeLine line) throws ApiError{
		Sql sql = new Sql("INSERT INTO time_line ( user_id, pet_id, text) VALUES (?,?,?)");
		    sql.addParam(line.user_id,line.pet_id,line.text);
		    Integer id = jdbcTemplate.saveObject(sql);
		    line.id=id;
	}
	public void remoteTimeline(Integer id,Integer userId) throws ApiError{
		Sql sql = new Sql("UPDATE time_line t SET t.state=? WHERE t.id=? AND t.user_id=?");
		    sql.addParam(BaseAttribute.LOGIC_FALSE,id,userId);
		    System.out.println(id+"   "+userId);
		    jdbcTemplate.updateObject(sql);
	}
	
	public void searchTimeLine(Page page,Integer userId,Integer petId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM time_line t WHERE t.user_id=? and t.state!=? ");
		    sql.addParam(userId,BaseAttribute.LOGIC_FALSE);
		    sql.append("AND", "t.pet_id", "=", petId);
		    sql.append(" ORDER BY t.id DESC");
		    jdbcTemplate.search(page, sql, TimeLine.class);
	}
	
	/**
	 * 我的回答列表中，如果提问者又追问问题后则显示追问问题!
	 * @return
	 * @throws ApiError 
	 */
	public AnswerInfo findZhueiWenAnswer(String sessionid,Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT a.*,u.ico_path,u.nick_name FROM answer_info a ,user_info u WHERE a.user_id=u.user_id AND a.sessionid=? AND a.user_id=? ORDER BY a.answer_id DESC LIMIT 1");
		    sql.addParam(sessionid,userId);
		    
		    System.out.println(sessionid+"  "+userId);
		    
		return jdbcTemplate.findObject(sql, AnswerInfo.class);
	}

	public AnswerInfo findAnswerInfo(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT a.*,u.ico_path,u.nick_name FROM answer_info a ,user_info u WHERE a.user_id=u.user_id AND a.answer_id=?");
		    sql.addParam(id);
		    return jdbcTemplate.findObject(sql, AnswerInfo.class);
	}
	public PetEvent findPetEvent(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT * FROM pet_event p WHERE p.id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, PetEvent.class);
	}
	public void savePetEvent(PetEvent pe) throws ApiError{
		Sql sql = new Sql("INSERT INTO pet_event (pet_id, type, event_time, label) values (?,?,?,?)");
		    sql.addParam(pe.pet_id,pe.type,pe.event_time,pe.label);
		    Integer id = jdbcTemplate.saveObject(sql);
		    pe.id=id;
	}
	public void updatePetEvent(PetEvent pe) throws ApiError{
		Sql sql = new Sql("UPDATE pet_event pe SET pe.event_time=?,pe.label=?,pe.type=? WHERE pe.id=?");
		    sql.addParam(pe.event_time,pe.label,pe.type,pe.id);
		jdbcTemplate.updateObject(sql);
	}
	public void deletePetEvent(Integer id) throws ApiError{
		Sql sql = new Sql("DELETE p FROM pet_event p  WHERE p.id=?");
		    sql.addParam(id);
		jdbcTemplate.updateObject(sql);
	}
	public List<PetEvent>  searchPetEvent(Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT p.*,pin.pet_name FROM pet_event p,pet_info pin WHERE p.pet_id=pin.pet_id AND pin.user_id=?");
		    sql.addParam(userId);
	    return jdbcTemplate.queryForList(sql, PetEvent.class);
	}
	public void saveRequestInfo(RequestInfo r) throws ApiError{
		Sql sql = new Sql("INSERT INTO request_info (user_id, question,type, create_time, state,score) VALUES (?,?,?,?,?,?)");
		    sql.addParam(r.user_id,r.question,r.type,r.create_time,r.state,r.score);
		    Integer id = jdbcTemplate.saveObject(sql);
		    r.id=id;
	}
	public void subScore(Integer userId,Integer score) throws ApiError{
        Sql sub = new Sql("UPDATE user_info u SET u.score=u.score-? WHERE u.user_id=?");
        sub.addParam(score,userId);
        jdbcTemplate.updateObject(sub);
	}
	
	public void saveOption(Option op) throws ApiError{
		Sql sql = new Sql("INSERT INTO `option` (request_id, option_text) VALUES (?,?)");
		    sql.addParam(op.request_id,op.option_text);
		   Integer id = jdbcTemplate.saveObject(sql);
		   op.option_id=id;
	}
	public void searchRequestInfo(Page page ,String state,Integer userId,String type) throws ApiError{
		Sql sql = new Sql("SELECT r.*,u.nick_name,u.ico_path,u.sex,(SELECT COUNT(1) FROM answer_info a WHERE a.answer_type=? and a.oid=r.id) as cnt  FROM request_info r,user_info u WHERE r.user_id=u.user_id ");
		    sql.addParam(BaseAttribute.answer_type.request.toString());
		    sql.append("AND", "r.state", "=", state);
		    sql.append("AND", "r.user_id", "=", userId);
		    sql.append("AND", "r.type", "=", type);
		    sql.append(" ORDER BY r.id desc");
		    jdbcTemplate.search(page, sql, RequestInfo.class);
	}
	/**
	 * 我参与的投票
	 * @param userId
	 * @throws ApiError 
	 */
	public void searchRequestInfo4Myvote(Page page,Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT r.*,u.nick_name,u.ico_path,u.sex  FROM request_info r,user_info u,`option` o,user_option uo WHERE u.user_id=r.user_id AND r.id=o.request_id AND uo.option_id=o.option_id AND uo.user_id=?");
		    sql.addParam(userId);
		jdbcTemplate.search(page, sql, RequestInfo.class);
	}
	
	
	
	
	public void searchRequestInfoByAw(Page page ,String state,Integer userId) throws ApiError{
		Sql sql = new Sql("SELECT r.*,u.nick_name,u.ico_path,(SELECT COUNT(1) FROM answer_info a WHERE a.answer_type=? and a.oid=r.id) as cnt    ");
	    sql.append("FROM request_info r,user_info u,answer_info ai ");
		sql.append("WHERE r.user_id=u.user_id AND ai.answer_type=? AND ai.oid=r.id AND ai.user_id=?");
		sql.addParam(BaseAttribute.answer_type.request.toString(),BaseAttribute.answer_type.request.toString(),userId);
	    sql.append("AND", "r.state", "=", state);
	    sql.append(" ORDER BY r.id desc");
	    
	    
	    System.out.println(sql.toString());
	    
	    jdbcTemplate.search(page, sql, RequestInfo.class);
	}
	public List<Campaign> queryForHome(String type,Integer num) throws ApiError{
		Sql sql = new Sql("SELECT c.* FROM campaign c WHERE c.play_type=? ");
		    sql.addParam(type);
		    sql.append(" ORDER BY c.seq DESC");
		return jdbcTemplate.queryForList(sql, Campaign.class, num);
	}
	public List<RequestInfo> queryRequestForHome(Integer num) throws ApiError{
		Sql sql = new Sql("SELECT r.*,u.*,(SELECT COUNT(1) FROM answer_info ai WHERE ai.oid=r.id AND ai.answer_type='request') AS cnt FROM request_info r ,user_info u WHERE r.user_id=u.user_id  ");
		return jdbcTemplate.queryForList(sql, RequestInfo.class,num);		
	}
	public RequestInfo findRequestInfo(Integer requestId) throws ApiError{
		Sql sql = new Sql("SELECT r.*,(SELECT COUNT(1) FROM answer_info ai WHERE ai.oid=r.id AND ai.answer_type='request') AS cnt FROM request_info r WHERE r.id=?");
		    sql.addParam(requestId);
	    return jdbcTemplate.findObject(sql, RequestInfo.class);
	}
	public List<Option> queryOptionByRequestId(Integer requestId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM  `option` o WHERE o.request_id=?");
		    sql.addParam(requestId);
		return jdbcTemplate.queryForList(sql, Option.class);
	}
	public String isVote(Integer userId,Integer requestId) throws ApiError{
		Sql sql = new Sql("SELECT '1' FROM user_option uo,`option` o WHERE uo.option_id=o.option_id AND  uo.user_id=? AND o.request_id=?");
		    sql.addParam(userId,requestId);
		    String isvote =jdbcTemplate.findForString(sql);
		    return BaseAttribute.LOGIC_TRUE.equals(isvote)?BaseAttribute.LOGIC_TRUE:BaseAttribute.LOGIC_FALSE;//isvote;
		    //return jdbcTemplate.findForString(sql);
	}
	public UserOption findUserOption(Integer userId,Integer requestId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_option uo,`option` o WHERE uo.option_id=o.option_id AND uo.user_id=? AND o.request_id=?");
		    sql.addParam(userId,requestId);
		    return jdbcTemplate.findObject(sql, UserOption.class);
		
	}
	public Option findOptionById(Integer id) throws ApiError{
		Sql sql = new Sql("SELECT * FROM `option` o WHERE o.option_id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, Option.class);
	}
	public void vote(Integer optionId,Integer userId) throws ApiError{
		Sql sql = new Sql("UPDATE `option` o SET o.times=o.times+1 WHERE o.option_id=?");
		    sql.addParam(optionId);
	        jdbcTemplate.updateObject(sql);
	        
	    Sql uosql = new Sql("INSERT INTO user_option (user_id, option_id) VALUES (?,?)");
	        uosql.addParam(userId,optionId);
	        jdbcTemplate.saveObject(uosql);
	}
	public void updateRequestInfoByAnswerId(Integer requestId,Integer answerId) throws ApiError{
		//
		Sql sql = new Sql("UPDATE request_info r SET r.state=? ,r.answer_id=? WHERE r.id=?");
	        sql.addParam(BaseAttribute.LOGIC_TRUE,answerId,requestId);
	        jdbcTemplate.updateObject(sql);
	    //add score
	        Integer score = Config.getInteger("base.score", 20);
	        
	        
	        Sql add = new Sql("UPDATE user_info u,request_info r,answer_info a SET u.score=u.score+r.score+"+score+" WHERE r.answer_id=a.answer_id AND u.user_id=a.user_id AND r.id=?");
	        add.addParam(requestId);
	        jdbcTemplate.updateObject(add);  
	    /*sub score 扣除积分在添加问题时就已经扣除
	        Sql sub = new Sql("UPDATE user_info u,request_info r SET u.score=u.score-r.score WHERE u.user_id=r.user_id AND r.id=?");
	        sub.addParam(requestId);
	        jdbcTemplate.updateObject(sub);
	    */
	}
	public UserTaskDaily findUserTaskDaily(Integer userId,String taskId,String date) throws ApiError{
	     Sql sql = new Sql("SELECT utd.* FROM user_task_daily utd WHERE utd.user_id=? AND utd.task_id=? AND utd.create_date=? ");	
	         sql.addParam(userId,taskId,date);
	     return jdbcTemplate.findObject(sql, UserTaskDaily.class);
	}

	public void saveUserTaskDaily(String date,Integer userId,String task_id) throws ApiError{
		Sql sql = new Sql("INSERT INTO user_task_daily (create_date, user_id, task_id,time) VALUES (?,?,?,1)");
		    sql.addParam(date,userId,task_id);
		    jdbcTemplate.updateObject(sql);
	}
	public void updateUserTaskDaily(String date,Integer userId,String task_id) throws ApiError{
		Sql sql = new Sql("UPDATE user_task_daily utd SET utd.time=utd.time+1,utd.received='0' WHERE utd.user_id=? AND utd.task_id=? AND utd.create_date=?");
		    sql.addParam(userId,task_id,date);
		    jdbcTemplate.updateObject(sql);
	}
	
	
	public TaskInfo findTaskInfo(String taskId,String taskType) throws ApiError{
		Sql sql = new Sql("SELECT * FROM task_info t WHERE t.task_id=? AND t.task_type=?");
		    sql.addParam(taskId,taskType);
		    return jdbcTemplate.findObject(sql, TaskInfo.class);
	}
	
	public UserTask findUserTask(Integer userId,String taskId) throws ApiError{
		Sql sql = new Sql("SELECT * FROM user_task ut WHERE ut.user_id=? AND ut.task_id=?");
		    sql.addParam(userId,taskId);
		return jdbcTemplate.findObject(sql, UserTask.class);
	}
	public void addTime4userTask(Integer userId,String taskId) throws ApiError{
		Sql sql = new Sql("UPDATE user_task ut SET ut.time=ut.time+1,ut.received='0' WHERE ut.user_id=? AND ut.task_id=?");
		    sql.addParam(userId,taskId);
		jdbcTemplate.updateObject(sql);
	}
	
	public void saveUserTask(Integer userId,String taskId,Integer time) throws ApiError{
		Sql sql = new Sql("INSERT INTO user_task (user_id,task_id,time) VALUES (?,?,?)");
		    sql.addParam(userId,taskId,time);
		    jdbcTemplate.updateObject(sql);
	}
	
	public void updateUserTask(Integer userId,String taskId) throws ApiError{
		Sql sql = new Sql("UPDATE user_task ut SET ut.time+1 WHERE ut.user_id=? AND ut.task_id=?");
		    sql.addParam(userId,taskId);
		    jdbcTemplate.updateObject(sql);
	}
	

    public List<TaskInfo> queryUserTaskInfo(Integer userId,String task_type) throws ApiError{
    	Sql sql = new Sql("SELECT ti.*,IFNULL(ut.time,0) AS done_time,IFNULL(ut.received,'0') received FROM task_info ti LEFT JOIN user_task ut ON ti.task_id=ut.task_id AND ut.user_id=? WHERE ti.task_type=?");
    	    sql.addParam(userId,task_type);
    	return jdbcTemplate.queryForList(sql, TaskInfo.class);
    }
    public List<TaskInfo> queryTaskDaily(Integer userId,String date,String task_type) throws ApiError{
    	Sql sql = new Sql("SELECT ti.*,IFNULL(utd.time,0) AS done_time,IFNULL(utd.received,'0') received FROM task_info ti LEFT JOIN user_task_daily utd ON ti.task_id=utd.task_id AND utd.user_id=? AND utd.create_date=? WHERE ti.task_type=?");
    	    sql.addParam(userId,date,task_type);
    	return jdbcTemplate.queryForList(sql, TaskInfo.class);
    }
    
    
    public UserTaskDaily findDailyTask(Integer userId,String date) throws ApiError{
    	Sql sql = new Sql("SELECT * FROM user_task_daily utd WHERE utd.user_id=? AND utd.create_date=?");
    	    sql.addParam(userId,date);
    	return jdbcTemplate.findObject(sql, UserTaskDaily.class);
    }
	public void addScroeByUserTask(Integer userId,String taskId,TaskInfo task) throws ApiError{
		Sql sql = new Sql("UPDATE user_task ut SET ut.received=? WHERE ut.user_id=? AND ut.task_id=?");
	    sql.addParam(BaseAttribute.LOGIC_TRUE,userId,taskId);
        jdbcTemplate.updateObject(sql);
    Sql usql = new Sql("UPDATE user_info ui SET ui.score=ui.score+"+task.task_integer+" WHERE ui.user_id=?");
        usql.addParam(userId);
        jdbcTemplate.updateObject(usql);
	}
	
	public void addScroeByUserTaskDaily(String date,Integer userId,String taskId,TaskInfo task) throws ApiError{
		Sql sql = new Sql("UPDATE user_task_daily utd SET utd.received=? WHERE utd.user_id=? AND utd.task_id=? AND utd.create_date=?");
		    sql.addParam(BaseAttribute.LOGIC_TRUE,userId,taskId,date);
	        jdbcTemplate.updateObject(sql);
	    Sql usql = new Sql("UPDATE user_info ui SET ui.score=ui.score+"+task.task_integer+" WHERE ui.user_id=?");
	        usql.addParam(userId);
	        jdbcTemplate.updateObject(usql);
	}
}
